/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package ijournalimporter.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author eric
 */
public class DBHelper {

    private Connection con = null;
    private boolean ready = false;
    private String host;
    private String dbname;
    private String username;
    private String password;
    private boolean exception = true;
    private ArrayList openedConnectionList = new ArrayList();

    public DBHelper(String host, String dbname, String username, String password) {
        this.host = host;
        this.dbname = dbname;
        this.username = username;
        this.password = password;

        connect();
    }

    public DBHelper(String host, String dbname, String username, String password, boolean showException) {
        this.host = host;
        this.dbname = dbname;
        this.username = username;
        this.password = password;
        this.exception = showException;

        connect();
    }

    public ArrayList<HashMap> readToHashMap(String qry) {
        try {
            ArrayList<HashMap> result = new ArrayList();
            ResultSet rs = executeQuery(qry);
            if (rs == null) {
                return null;
            }

            ResultSetMetaData rsmd = rs.getMetaData();
            while (rs.next()) {
                HashMap row = new HashMap();
                for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                    row.put(rsmd.getColumnName(i), getDataFromResultSet(rs, i));
                }
                result.add(row);
            }
            return result;
        } catch (SQLException ex) {
            Logger.getLogger(DBHelper.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

    public ArrayList<ArrayList> readToArray(String qry) {
        try {
            ArrayList<ArrayList> result = new ArrayList();
            ResultSet rs = executeQuery(qry);
            if (rs == null) {
                return null;
            }

            ResultSetMetaData rsmd = rs.getMetaData();
            while (rs.next()) {
                ArrayList row = new ArrayList();
                for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                    row.add(getDataFromResultSet(rs, i));
                }
                result.add(row);
            }
            return result;
        } catch (SQLException ex) {
            Logger.getLogger(DBHelper.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

    private String getDataFromResultSet(ResultSet rs, int column) {
        try {
            String rval = null;
            Object obj = rs.getObject(column);
            if (obj != null) {
                rval = obj.toString();
            }
            return rval;
        } catch (SQLException ex) {
            Logger.getLogger(DBHelper.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

    public boolean isReady() {
        return ready;
    }

    public Connection getConnection() {
        return con;
    }

    public ResultSet executeQuery(String qry) {
        ResultSet rs = null;
        Statement stat = this.getStatement();

        if (stat != null) {
            try {
                rs = stat.executeQuery(qry);
                this.openedConnectionList.add(rs);
                return rs;
            } catch (SQLException ex) {
                if (exception) {
                    Logger.getLogger(DBHelper.class.getName()).log(Level.SEVERE, null, ex);
                }
                return null;
            }
        } else {
            return null;
        }
    }

    public int excuteUpdate(String qry) {
        Statement stat = this.getStatement();

        if (stat != null) {
            try {
                this.openedConnectionList.add(stat);
                return stat.executeUpdate(qry);
            } catch (SQLException ex) {
                if (exception) {
                    Logger.getLogger(DBHelper.class.getName()).log(Level.SEVERE, null, ex);
                }
                return -1;
            }
        } else {
            return -1;
        }
    }

    public ResultSet getUpdatableResultSet(String qry) {
        try {
            // Create a statement that will return updatable result sets
            Statement stmt = con.createStatement(
                    ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

            this.openedConnectionList.add(stmt);
            // Primary key col_string must be specified so that the result set is updatable
            ResultSet rs = stmt.executeQuery(qry);
            this.openedConnectionList.add(rs);
            return rs;
        } catch (SQLException ex) {
            if (exception) {
                Logger.getLogger(DBHelper.class.getName()).log(Level.SEVERE, null, ex);
            }
            return null;
        }
    }

    public PreparedStatement getPreparedStatement(String pqry) {
        try {
            PreparedStatement pstmt = con.prepareStatement(pqry);
            openedConnectionList.add(pstmt);
            return pstmt;
        } catch (SQLException ex) {
            if (exception) {
                Logger.getLogger(DBHelper.class.getName()).log(Level.SEVERE, null, ex);
            }

            return null;
        }
    }

    public PreparedStatement getPreparedStatement(String tablename, int colNum) {
        StringBuffer sb = new StringBuffer();
        sb.append("INSERT INTO ");
        sb.append(tablename);
        sb.append(" VALUES(");
        for (int i = 1; i < colNum; i++) {
            sb.append("?,");
        }
        sb.append("?)");
        return getPreparedStatement(sb.toString());
    }

    public boolean isAutocommit() {
        try {
            return con.getAutoCommit();
        } catch (SQLException ex) {
            if (exception) {
                Logger.getLogger(DBHelper.class.getName()).log(Level.SEVERE, null, ex);
            }
            return false;
        }
    }

    public void setAutocommit(boolean commit) {
        try {
            con.setAutoCommit(commit);
        } catch (SQLException ex) {
            if (exception) {
                Logger.getLogger(DBHelper.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }

    public void commit() {
        if (isAutocommit())
            return;

        try {
            con.commit();
        } catch (SQLException ex) {
            if (exception) {
                Logger.getLogger(DBHelper.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }

    public Statement getStatement() {
        if (con == null) {
            return null;
        }

        Statement stat = null;
        try {
            stat = con.createStatement();
            return stat;
        } catch (SQLException ex) {
            if (exception) {
                Logger.getLogger(DBHelper.class.getName()).log(Level.SEVERE, null, ex);
            }
            return null;
        }

    }

    public boolean rollback() {
        if (con != null) {
            try {
                con.rollback();
                return true;
            } catch (SQLException ex) {
                Logger.getLogger(DBHelper.class.getName()).log(Level.SEVERE, null, ex);
                return false;
            }
        } else {
            return false;
        }
    }

    public void close() {
        try {
            this.finalize();
        } catch (Throwable ex) {
            if (exception) {
                Logger.getLogger(DBHelper.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }


    @Override
    protected void finalize() throws Throwable {
        for (int i = 0; i < this.openedConnectionList.size(); i++) {
            if (this.openedConnectionList.get(i) instanceof ResultSet) {
                ResultSet rs = (ResultSet) this.openedConnectionList.get(i);
                try {
                    rs.close();
                } catch (Exception e) {
                    if (exception) {
                        Logger.getLogger(DBHelper.class.getName()).log(Level.SEVERE, null, e);
                    }
                }
            } else if (this.openedConnectionList.get(i) instanceof Statement) {
                Statement stat = (Statement) this.openedConnectionList.get(i);
                try {
                    stat.close();
                } catch (Exception e) {
                    if (exception) {
                        Logger.getLogger(DBHelper.class.getName()).log(Level.SEVERE, null, e);
                    }
                }
            } else if (this.openedConnectionList.get(i) instanceof PreparedStatement) {
                PreparedStatement pstat = (PreparedStatement) this.openedConnectionList.get(i);
                try {
                    pstat.close();
                } catch (Exception e) {
                    if (exception) {
                        Logger.getLogger(DBHelper.class.getName()).log(Level.SEVERE, null, e);
                    }
                }
            }
        }

        if (con != null) {
            try {
                con.close();
                con = null;
            } catch (Exception e) {
                if (exception) {
                    Logger.getLogger(DBHelper.class.getName()).log(Level.SEVERE, null, e);
                }
            }

            openedConnectionList.clear();
            ready = false;
            super.finalize();
        }
    }

    public void setShowException(boolean ex) {
        exception = ex;
    }

    public int countResultSet(ResultSet rs) {
        int counter = 0;
        if (rs != null) {
            try {
                while (rs.next()) {
                    counter++;
                }
                rs.first();
            } catch (SQLException ex) {
                Logger.getLogger(DBHelper.class.getName()).log(Level.SEVERE, null, ex);
                return 0;
            }
        }
        return counter;
    }

    private boolean connect() {
        try {
            if (host == null || dbname == null || username == null || password == null) {
                return false;
            }

            //Class.forName("org.gjt.mm.mysql.Driver");
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://" + host + "/" + dbname + "?user=" + username + "&password=" + password + "&useUnicode=true&characterEncoding=UTF-8");
            ready = true;
            return ready;
        } catch (SQLException ex) {
            if (exception) {
                Logger.getLogger(DBHelper.class.getName()).log(Level.SEVERE, null, ex);
            }
            return false;
        } catch (ClassNotFoundException ex) {
            if (exception) {
                Logger.getLogger(DBHelper.class.getName()).log(Level.SEVERE, null, ex);
            }
            return false;
        }
    }
}
